﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.Windows;
using System.Drawing;
using System.Diagnostics.Contracts;

namespace MoneyManager
{
    public enum Tables { Categories, Transactions };

    static class DBManager
    {

        static string connectionString = "server=127.0.0.1; user id=root; password=1111; database=simplemoneymanager";
        static private MySqlConnection _connection;

        public static void TablesInitialisation()
        {
            Connect();
            DataTable categoryDT = LoadTable(Tables.Categories);
            DataTable transactionsDT = LoadTable(Tables.Transactions);

            foreach (DataRow r in categoryDT.Rows)
            {
                CategoryManager.Add(CategoriesRowParse(r));
            }

            foreach (DataRow r in transactionsDT.Rows)
            {
                TransactionManager.Transactions.Add(TransactionsRowParse(r));
            }
        }

        private static Category CategoriesRowParse(DataRow row)
        {
            Category category = new Category();
            try
            {
                category.Id = (int)row[0];
                category.Name = (String)row.ItemArray[1];
                category.Image =  row.ItemArray[2] == DBNull.Value ? "" : (String)row.ItemArray[2];
                category.Description = row.ItemArray[3] == DBNull.Value ? "" : (String)row.ItemArray[3];
                category.Type = GetCategoryTypeByDBIndex(row[4]);
            }
            catch (Exception e)
            {
                Log.WriteError("Categories table row parsing error: " + e.Message);
            }
            return category;
        }

        private static CategoryType GetCategoryTypeByDBIndex(object p)
        {
            int i = int.Parse(p.ToString());
            return i == (int)CategoryType.Expense ? CategoryType.Expense : i == (int)CategoryType.Income ? CategoryType.Income : CategoryType.Both;
        }
       
        private static Transaction TransactionsRowParse(DataRow row)
        {
            Transaction transaction = new Transaction();
            try
            {
                transaction.Id = (int)row.ItemArray[0];
                transaction.Category =  CategoryManager.GetCategoryById((int)row.ItemArray[1]) ;
                transaction.Description = (String)row.ItemArray[2];
                transaction.Amount = Double.Parse(row.ItemArray[3].ToString());
                transaction.Date = DateTime.Parse(row.ItemArray[4].ToString());
                transaction.Notes = row.ItemArray[5] == DBNull.Value ? "" : (String)row.ItemArray[5];
                transaction.Currency = GetCurrencySymbol(row.ItemArray[6]);
                transaction.Type = GetTransactionTypeByDBIndex(row.ItemArray[7]);
            }
            catch (Exception e)
            {
                Log.WriteError("Transactions table row parsing error: " + e.Message);
            }
            return transaction;
        }

        private static TransactionType GetTransactionTypeByDBIndex(object p)
        {
            int i = int.Parse(p.ToString());
            return i == (int)TransactionType.Expense ? TransactionType.Expense : TransactionType.Income;
        }

        private static string GetCurrencySymbol(object p)
        {
            return "₴ ";
        }

        public static bool Connect()
        {
            Log.WriteInfo("Connecting to Database");
            try
            {
                _connection = new MySqlConnection(connectionString);
                _connection.Open();
                Log.WriteInfo("Database connection is established");
            }
            catch (Exception ex)
            {
                Log.WriteError("Error database connection");
                MessageBox.Show("Error database connection", "Error");
                _connection = null;
                return false;
            }

            return true;
        }

        public static void DisConnect()
        {
            _connection.Close();
            _connection = null;
        }

        private static DataTable LoadTable(Tables table)
        {
            DataTable res = new DataTable();
            try
            {
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM " + table.ToString(), _connection);
                new MySqlCommandBuilder(da);
                da.Fill(res);
                da.ContinueUpdateOnError = true;
                Log.WriteInfo("Table " + table.ToString() + " loaded successfully");
            }
            catch (Exception e)
            {
                Log.WriteError("Error in table " + table.ToString() + " loading:" + e.Message);
            }
            return res;
        }

        private static int CategoryTypeIndexInDb(CategoryType type)
        {
            return type == CategoryType.Expense ? 0 : type == CategoryType.Income ? 1 : 2;
        }

        private static int TransactionTypeIndexInDB(TransactionType type)
        {
            return type == TransactionType.Expense ? 0 : 1;
        }

        public static void Insert(Category category)
        {
            //try
            //{
                Contract.Requires(category.Id == -1, "category.Id is not -1");

                String insertString = 
                    String.Format("INSERT INTO Categories (name, image, description, type) VALUES ('{0}', '{1}', '{2}', {3})",
                    category.Name, category.Image, category.Description, CategoryTypeIndexInDb(category.Type));
             
                MySqlCommand cmd = new MySqlCommand(insertString, _connection);
                cmd.ExecuteNonQuery();
            //}
            //catch (Exception e)
            //{
            //    Log.WriteError("Insert error in Categories table" + e.Message);
            //}
        }

        public static void Insert(Transaction transaction)
        {
            //try
            //{
            Contract.Requires(transaction.Id == -1, "transaction.Id shoul be -1");
            Contract.Requires(transaction.Category != null && transaction.Amount != null &&
                     transaction.Description != null, "Some field in transaction is null");
               
                Double? amount = transaction.Amount;
                if (transaction.Type == TransactionType.Expense)
                    amount *= -1;

                String insertString = 
                    String.Format("INSERT INTO Transactions (category_id, description, amount, date, notes, type) VALUES ({0}, '{1}', {2}, '{3}', '{4}', {5})", 
                    transaction.Category.Id, transaction.Description, (Decimal)amount, transaction.Date.ToString("yyyy-MM-dd"), 
                    transaction.Notes, TransactionTypeIndexInDB(transaction.Type));
           
            MySqlCommand cmd = new MySqlCommand(insertString, _connection);
            cmd.ExecuteNonQuery();
            //}
            //catch (Exception e)
            //{
            //    Log.WriteError("Insert error in Transactions table" + e.Message);
            //}
        }

        public static void Update(Category category)
        {
            try
            {
                String updateString =
                    String.Format("UPDATE Categories SET name = '{0}', image = '{1}', description = '{2}', type = {3} WHERE id = {4}",
                    category.Name, category.Image, category.Description, CategoryTypeIndexInDb(category.Type), category.Id);

                MySqlCommand cmd = new MySqlCommand(updateString, _connection);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            { 
                Log.WriteError("Error in updating Categpries table: " + e.Message);
            }
        }

        public static void Update(Transaction transaction)
        {
            try
            {
                String updateString = 
                    String.Format("UPDATE Transactions SET category_id = {0}, description = '{1}', amount = {2}, date = '{3}', notes = '{4}', type = {5} WHERE id = {6}",
                    transaction.Category.Id, transaction.Description, (Decimal)transaction.Amount, transaction.Date.ToString("yyyy-MM-dd"), transaction.Notes, 
                    TransactionTypeIndexInDB(transaction.Type), transaction.Id);

                MySqlCommand cmd = new MySqlCommand(updateString, _connection);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.WriteError("Error in updating Transactions table: " + e.Message);
            }
        }

        public static void Delete(Tables table, int id)
        {
            try
            {
                String deleteString =
                    "DELETE FROM " + table.ToString() +
                    "WHERE id = " + id.ToString();

                MySqlCommand cmd = new MySqlCommand(deleteString, _connection);
                cmd.ExecuteNonQuery();
            } 
            catch (Exception e)
            {
                Log.WriteError("Error in deliting field from " + table.ToString() + " table: " + e.Message);
            }
        }
    }
    
}
